/**  
 * 文 件 名: RoleDAO.java  
 * 描    述：   
 * 版    权：Copyright (c)20012-2012 tongzhou All Rights Reserved.
 * 公    司: 同舟 
 * 作    者：彭定雄                     
 * 版    本: 1.0  
 * 创建时间: 2012-10-27
 *  
 * 修改历史：  
 * 时间                             作者                       版本                        描述  
 * ------------------------------------------------------------------  
 * 2012-10-27        彭定雄                1.0          1.0 Version  
 */       

/**
 * 
 */
package com.tongzhou.system.rightsMgmt.dao.impl;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.tongzhou.db.DBUtil;
import com.tongzhou.db.exception.DBException;
import com.tongzhou.system.menu.model.Menu;
import com.tongzhou.system.rightsMgmt.dao.IRoleDAO;
import com.tongzhou.system.rightsMgmt.model.Groups;
import com.tongzhou.system.rightsMgmt.model.Right;
import com.tongzhou.system.rightsMgmt.model.RightType;
import com.tongzhou.system.rightsMgmt.model.Role;
import com.tongzhou.system.rightsMgmt.model.RoleRightTypeRelation;
import com.tongzhou.system.rightsMgmt.model.SearchRoleCondition;
import com.tongzhou.system.userMgr.model.User;
import com.tongzhou.util.Util;

public class RoleDAO implements IRoleDAO{

	@Override
	public void addRole(Role role) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("insert into sys_role(name,active,create_by,create_time,update_by,update_time) ");
		sql.append("values('"+role.getName()+"','"+role.getActive()+"',");
		sql.append("'"+role.getCreate_by()+"',now(),");
		sql.append("'"+role.getCreate_by()+"',now())");
		DBUtil.update(sql.toString());
	}

	@Override
	public List<Role> checkRoleByName(Role role) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select name from sys_role where name='"+role.getName()+"' and id!='"+role.getId()+"'");
		List<Role> result=DBUtil.query(sql.toString(),new BeanListHandler<Role>(Role.class));
		return result;
	}
	
	@Override
	public void deleteRole(Role role) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("delete from sys_role where id='"+role.getId()+"'");
		DBUtil.update(sql.toString());
		
		sql.delete(0, sql.length());
		sql.append("delete from sys_role_menu_relation where role_id='"+role.getId()+"'");
		DBUtil.update(sql.toString());
		
		sql.delete(0, sql.length());
		sql.append("delete from sys_role_right_relation where role_id='"+role.getId()+"'");
		DBUtil.update(sql.toString());
		
		sql.delete(0, sql.length());
		sql.append("delete from sys_role_groups_relation where role_id='"+role.getId()+"'");
		DBUtil.update(sql.toString());
	}

	@Override
	public Role getRoleById(Role role) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select r.id,r.name,r.active,r.create_time,r.create_by,r.update_time,r.update_by,u1.name as creator,u2.name as updator ");
		sql.append(" from sys_role r left join sys_user u1 on u1.id=r.create_by ");
		sql.append(" left join sys_user u2 on u2.id=r.update_by ");
		sql.append(" where 1=1 ");
		sql.append(" and r.id='"+role.getId()+"'");
		Role role1=DBUtil.query(sql.toString(), new BeanHandler<Role>(Role.class));
		return role1;
	}

	@Override
	public List<Role> listRole(SearchRoleCondition condition)
			throws DBException {
		List<Role> result=null;
		//查询条件SQL
		//查询参数
		StringBuffer sqlCondition = new StringBuffer() ;
		List<String> paramList = new ArrayList<String>() ;
		
		if(!Util.checkObjIsNull(condition.getName())){
			sqlCondition.append(" and r.name like ?") ;
			paramList.add("%"+condition.getName()+"%") ;
		}
		if(!Util.checkObjIsNull(condition.getActive())){
			sqlCondition.append(" and r.active=?");
			paramList.add(condition.getActive());
		}
		
		//取得满足条件的总记录数和总页数
		StringBuffer sql = new StringBuffer() ;
		sql.append("select count(*) from sys_role r ");
		sql.append(" where 1=1 ");
		sql.append(sqlCondition);
		
		//总记录数
		long total = DBUtil.getCount(sql.toString(),paramList.toArray()) ;
		//总页数
		long totalPage = Util.getTotalPage(total, condition.getPageSize());
		
		condition.setTotal(total);
		condition.setTotalPage(totalPage);
		
		//查询某页的列表数据
		sql.delete(0, sql.length());
		sql.append("select r.id,r.name,r.active,r.create_time,r.create_by,r.update_time,r.update_by,u1.name as creator,u2.name as updator ");
		sql.append(" from sys_role r left join sys_user u1 on u1.id=r.create_by ");
		sql.append(" left join sys_user u2 on u2.id=r.update_by ");
		sql.append(" where 1=1 ");
		sql.append(sqlCondition);
		sql.append(" order by "+condition.getOrderBy()+" "+condition.getOrderByType()) ;
		result = DBUtil.query(sql.toString(), new BeanListHandler<Role>(Role.class), paramList.toArray(), Util.getStartPos(condition.getCurrentPage(), condition.getPageSize()), condition.getPageSize()) ;
		return result;
	}

	@Override
	public void updateRole(Role role) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("update sys_role set ");
		sql.append("name='"+role.getName()+"',active='"+role.getActive()+"',");
		sql.append("update_by='"+role.getUpdate_by()+"',update_time=now()");
		sql.append(" where id='"+role.getId()+"' ");
		DBUtil.update(sql.toString());
	}

	@Override
	public List<Menu> getRoleMenuById(Role role) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select menu_id as id from sys_role_menu_relation where role_id='"+role.getId()+"'");
		List<Menu> result=DBUtil.query(sql.toString(),new BeanListHandler<Menu>(Menu.class));
		return result;
	}

	@Override
	public void updateRoleMenu(Role role) throws DBException {
		String[] menuIds=role.getItemsVal().split("`");
		StringBuffer sql=new StringBuffer();
		sql.append("delete from sys_role_menu_relation where role_id='"+role.getId()+"'");
		DBUtil.update(sql.toString());
		if(!Util.checkObjIsNull(role.getItemsVal())){
			for(int i=0;i<menuIds.length;i++){
				sql.delete(0, sql.length());
				sql.append("insert into sys_role_menu_relation(role_id,menu_id) ");
				sql.append(" values('"+role.getId()+"','"+menuIds[i]+"'); ");
				DBUtil.update(sql.toString());
			}
		}
	}

	@Override
	public void updateRoleRight(Role role) throws DBException {
		String[] rightIds=role.getItemsVal().split("`");
		StringBuffer sql=new StringBuffer();
		sql.append("delete from sys_role_right_relation where role_id='"+role.getId()+"'");
		DBUtil.update(sql.toString());
		if(!Util.checkObjIsNull(role.getItemsVal())){
			for(int i=0;i<rightIds.length;i++){
				sql.delete(0, sql.length());
				sql.append("insert into sys_role_right_relation(role_id,right_id) ");
				sql.append(" values('"+role.getId()+"','"+rightIds[i]+"'); ");
				DBUtil.update(sql.toString());
			}
		}
	}

	@Override
	public List<Right> getRoleRightById(Role role) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select right_id as id from sys_role_right_relation where role_id='"+role.getId()+"'");
		List<Right> result=DBUtil.query(sql.toString(),new BeanListHandler<Right>(Right.class));
		return result;
	}

	@Override
	public List<User> getRoleUserById(Role role) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select u.user_id as id,u2.username from sys_user_role_relation u inner join sys_user u2 on u2.id=u.user_id where u.role_id='"+role.getId()+"'");
		List<User> result=DBUtil.query(sql.toString(),new BeanListHandler<User>(User.class));
		return result;
	}

	@Override
	public List<Role> getAllRole() throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select r.id,r.name ");
		sql.append(" from sys_role r ");
		sql.append(" where 1=1 ");
		List<Role> result=DBUtil.query(sql.toString(),new BeanListHandler<Role>(Role.class));
		return result;
	}

	@Override
	public List<Role> getUserRoleByUserId(Role role) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select u.role_id as id,r.name ");
		sql.append(" from sys_user_role_relation u ");
		sql.append(" inner join sys_role r on r.id=u.role_id ");
		sql.append(" where 1=1 ");
		sql.append(" and u.user_id='"+role.getUserID()+"'");
		List<Role> result=DBUtil.query(sql.toString(),new BeanListHandler<Role>(Role.class));
		return result;
	}
	
	@Override
	public void updateUserRole(Role role) throws DBException {
		String[] strs=role.getItemsVal().split("`");
		StringBuffer sql=new StringBuffer();
		sql.append("delete from sys_user_role_relation where user_id='"+role.getUserID()+"'");
		DBUtil.update(sql.toString());
		if(!Util.checkObjIsNull(role.getItemsVal())){
			for(int i=0;i<strs.length;i++){
				sql.delete(0, sql.length());
				sql.append("insert into sys_user_role_relation(role_id,user_id) ");
				sql.append(" values('"+strs[i]+"','"+role.getUserID()+"'); ");
				DBUtil.update(sql.toString());
			}
		}
	}
	
	@Override
	public void updateRoleUser(Role role) throws DBException {
		String[] strs=role.getItemsVal().split("`");
		StringBuffer sql=new StringBuffer();
		sql.append("delete from sys_user_role_relation where role_id='"+role.getId()+"'");
		DBUtil.update(sql.toString());
		if(!Util.checkObjIsNull(role.getItemsVal())){
			for(int i=0;i<strs.length;i++){
				sql.delete(0, sql.length());
				sql.append("insert into sys_user_role_relation(role_id,user_id) ");
				sql.append(" values('"+role.getId()+"','"+strs[i]+"'); ");
				DBUtil.update(sql.toString());
			}
		}
	}

	@Override
	public List<Groups> getRoleGroupsById(Role role) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select u.groups_id as id,r.name ");
		sql.append(" from sys_role_groups_relation u ");
		sql.append(" inner join sys_groups r on r.id=u.groups_id ");
		sql.append(" where 1=1 ");
		sql.append(" and u.role_id='"+role.getId()+"'");
		List<Groups> result=DBUtil.query(sql.toString(),new BeanListHandler<Groups>(Groups.class));
		return result;
	}

	@Override
	public void updateRoleGroups(Role role) throws DBException {
		String[] strs=role.getItemsVal().split("`");
		StringBuffer sql=new StringBuffer();
		sql.append("delete from sys_role_groups_relation where role_id='"+role.getId()+"'");
		DBUtil.update(sql.toString());
		if(!Util.checkObjIsNull(role.getItemsVal())){
			for(int i=0;i<strs.length;i++){
				sql.delete(0, sql.length());
				sql.append("insert into sys_role_groups_relation(role_id,groups_id) ");
				sql.append(" values('"+role.getId()+"','"+strs[i]+"'); ");
				DBUtil.update(sql.toString());
			}
		}
	}

	@Override
	public List<RoleRightTypeRelation> getRTDataById(RightType rightType) throws DBException {
		StringBuffer sql=new StringBuffer();
		String name="name";
		sql.append("select id as dataID,"+name+" as dataName from "+rightType.getTable_name());
		List<RoleRightTypeRelation> result=DBUtil.query(sql.toString(),new BeanListHandler<RoleRightTypeRelation>(RoleRightTypeRelation.class));
		return result;
	}

	@Override
	public void updateRoleRightType(Role role) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("delete from sys_role_right_type_relation where role_id='"+role.getId()+"'");
		DBUtil.update(sql.toString());
		
		RightType r1=DBUtil.query("select id from sys_right_type where table_name='comp_company_level1'", new BeanHandler<RightType>(RightType.class));
		RightType r2=DBUtil.query("select id from sys_right_type where table_name='comp_company_level2'", new BeanHandler<RightType>(RightType.class));
		RightType r3=DBUtil.query("select id from sys_right_type where table_name='comp_company_level3'", new BeanHandler<RightType>(RightType.class));
		String c1="";
		String c2="";
		String c3="";
		if(!Util.checkObjIsNull(r1.getId())){
			c1=r1.getId();
		}
		if(!Util.checkObjIsNull(r2.getId())){
			c2=r2.getId();
		}
		if(!Util.checkObjIsNull(r3.getId())){
			c3=r3.getId();
		}
		
		if(!Util.checkObjIsNull(role.getItemsVal())){
			String[] strs=role.getItemsVal().split("※");
			for(int i=0;i<strs.length;i++){
				String[] strs2=strs[i].split("`");
				for(int j=0;j<strs2.length;j++){
					String[] datas=strs2[j].split(";");
					sql.delete(0, sql.length());
					sql.append("insert into sys_role_right_type_relation(role_id,right_type_id,data_id) ");
					sql.append(" values('"+role.getId()+"','"+datas[1]+"','"+datas[0]+"'); ");
					DBUtil.update(sql.toString());
					if(datas[1].equals(c1)){
						sql.delete(0, sql.length());
						sql.append("insert into sys_role_right_type_relation(role_id,right_type_id,data_id,active,display) ");
						sql.append(" (select '"+role.getId()+"','"+c2+"',id,'Y','N' from comp_company_level2 where level1_id='"+datas[0]+"'); ");
						DBUtil.update(sql.toString());
						
						sql.delete(0, sql.length());
						sql.append("insert into sys_role_right_type_relation(role_id,right_type_id,data_id,active,display) ");
						sql.append(" (select '"+role.getId()+"','"+c3+"',c3.id,'Y','N' from comp_company_level3 c3 inner join comp_company_level2 c2 on c2.id=c3.level2_id where c2.level1_id='"+datas[0]+"');");
						DBUtil.update(sql.toString());
					}
					if(datas[1].equals(c2)){
						sql.delete(0, sql.length());
						sql.append("insert into sys_role_right_type_relation(role_id,right_type_id,data_id,active,display) ");
						sql.append(" (select '"+role.getId()+"','"+c1+"',c1.id,'N','N' from comp_company_level1 c1 inner join comp_company_level2 c2 on c2.level1_id=c1.id where c2.id='"+datas[0]+"'); ");
						DBUtil.update(sql.toString());
						
						sql.delete(0, sql.length());
						sql.append("insert into sys_role_right_type_relation(role_id,right_type_id,data_id,active,display) ");
						sql.append(" (select '"+role.getId()+"','"+c3+"',id,'Y','N' from comp_company_level3 where level2_id='"+datas[0]+"'); ");
						DBUtil.update(sql.toString());
					}
					if(datas[1].equals(c3)){
						sql.delete(0, sql.length());
						sql.append("insert into sys_role_right_type_relation(role_id,right_type_id,data_id,active,display) ");
						sql.append(" ( select '"+role.getId()+"','"+c1+"',c1.id,'N','N' from comp_company_level1 c1  ");
						sql.append(" inner join comp_company_level2 c2 on c2.level1_id=c1.id  ");
						sql.append(" inner join comp_company_level3 c3 on c3.level2_id=c2.id ");
						sql.append(" where c3.id='"+datas[0]+"') ");
						DBUtil.update(sql.toString());
						
						sql.delete(0, sql.length());
						sql.append("insert into sys_role_right_type_relation(role_id,right_type_id,data_id,active,display) ");
						sql.append("( select '"+role.getId()+"','"+c2+"',c2.id,'N','N' from comp_company_level2 c2 inner join comp_company_level3 c3 on c3.level2_id=c2.id where c3.id='"+datas[0]+"')");
						DBUtil.update(sql.toString());
					}
				}
			}
		}
	}

	@Override
	public List<RoleRightTypeRelation> getRTData(
			RoleRightTypeRelation roleRTRelation) throws DBException {
		StringBuffer sql=new StringBuffer();
		String name="name";
		sql.append("select id as dataID,"+name+" as dataName from "+roleRTRelation.getTable_name());
		List<RoleRightTypeRelation> result=DBUtil.query(sql.toString(),new BeanListHandler<RoleRightTypeRelation>(RoleRightTypeRelation.class));
		return result;
	}

	@Override
	public List<RoleRightTypeRelation> getRoleRTDataById(RightType rightType)
			throws DBException {
		StringBuffer sql=new StringBuffer();
		String name="name";
		sql.append("select rt.right_type_id,rt.data_id as dataID,c."+name+" as dataName ");
		sql.append(" from sys_role_right_type_relation rt ");
		sql.append(" inner join "+rightType.getTable_name()+" c on c.id=rt.data_id ");
		sql.append(" where rt.role_id='"+rightType.getRole_id()+"' and rt.display='Y'");
		sql.append(" and rt.right_type_id='"+rightType.getId()+"' order by rt.data_id ");
		List<RoleRightTypeRelation> result=DBUtil.query(sql.toString(),new BeanListHandler<RoleRightTypeRelation>(RoleRightTypeRelation.class));
		return result;
	}

	@Override
	public List<RightType> getRoleRTById(Role role) throws DBException {
		StringBuffer sql=new StringBuffer();
		sql.append("select distinct sr.table_name,sr.id,rt.role_id ");
		sql.append(" from sys_role_right_type_relation rt ");
		sql.append(" inner join sys_right_type sr on sr.id=rt.right_type_id ");
		sql.append(" where role_id='"+role.getId()+"' and rt.display='Y' order by sr.id");
		List<RightType> result=DBUtil.query(sql.toString(),new BeanListHandler<RightType>(RightType.class));
		return result;
	}
	
}
